IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Entities_InProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Entities_InProcess]
GO

CREATE PROCEDURE [dbo].[get_Entities_InProcess]

AS
SET NOCOUNT ON
BEGIN


Select ep.*,epl.Entities_InQue,epl.Entities_InProcess,epl.Entities_Processed,epl.Entities_Requested 
from vw_Queue_Entities_InProcess ep Left Join tbl_System_ProcessLog_Entities epl ON  ep.Process_ID = epl.Process_ID 

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Entities_InQueue]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Entities_InQueue]
GO

CREATE PROCEDURE [dbo].[get_Entities_InQueue]

AS
SET NOCOUNT ON
BEGIN

Select ep.*,epl.Entities_InQue,epl.Entities_InProcess,epl.Entities_Processed,epl.Entities_Requested  
from vw_Queue_Entities_InQueue   ep Left Join tbl_System_ProcessLog_Entities epl ON  ep.Process_ID = epl.Process_ID 

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Entities_Processed]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Entities_Processed]
GO

CREATE PROCEDURE [dbo].[get_Entities_Processed]

AS
SET NOCOUNT ON
BEGIN

Select ep.*,epl.Entities_InQue,epl.Entities_InProcess,epl.Entities_Processed,epl.Entities_Requested  
from vw_Queue_Entities_Processed    ep Left Join tbl_System_ProcessLog_Entities epl ON  ep.Process_ID = epl.Process_ID 

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Entities_ToProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Entities_ToProcess]
GO

CREATE PROCEDURE [dbo].[get_Entities_ToProcess]

AS
SET NOCOUNT ON
BEGIN

Declare @CurrentTime as datetime;
SET @CurrentTime = GETDATE();

Select ep.*,@CurrentTime as Entities_InQue,epl.Entities_InProcess,epl.Entities_Processed,epl.Entities_Requested 
from vw_Queue_Entities_ToProcess  ep Left Join tbl_System_ProcessLog_Entities epl ON  ep.Process_ID = epl.Process_ID 


Update dbo.tbl_System_ProcessLog_Entities
SET Entities_InQue = @CurrentTime
from vw_Queue_Entities_ToProcess  ep INNER JOIN tbl_System_ProcessLog_Entities epl ON  ep.Process_ID = epl.Process_ID 

END
GO


----------------------------   Securities ---------------------------------------------


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Securities_InProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Securities_InProcess]
GO

CREATE PROCEDURE [dbo].[get_Securities_InProcess]
(
@Process_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Select ep.*,epl.SecGroup_InQue,epl.SecGroup_InProcess,epl.SecGroup_Processed,epl.SecGroup_Requested 
from vw_Queue_Securities_InProcess ep Left Join tbl_System_ProcessLog_SecGroups epl ON  ep.SecOrder_ID = epl.SecOrder_ID 
Where ep.Process_ID = ISNULL(@Process_ID,ep.Process_ID)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Securities_InQueue]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Securities_InQueue]
GO

CREATE PROCEDURE [dbo].[get_Securities_InQueue]
(
@Process_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Select ep.*,epl.SecGroup_InQue,epl.SecGroup_InProcess,epl.SecGroup_Processed,epl.SecGroup_Requested 
from vw_Queue_Securities_InQueue   ep Left Join tbl_System_ProcessLog_SecGroups epl ON  ep.SecOrder_ID = epl.SecOrder_ID 
Where ep.Process_ID = ISNULL(@Process_ID,ep.Process_ID)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Securities_Processed]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Securities_Processed]
GO

CREATE PROCEDURE [dbo].[get_Securities_Processed]
(
@Process_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Select ep.*,epl.SecGroup_InQue,epl.SecGroup_InProcess,epl.SecGroup_Processed,epl.SecGroup_Requested 
from vw_Queue_Securities_Processed    ep Left Join tbl_System_ProcessLog_SecGroups epl ON  ep.SecOrder_ID = epl.SecOrder_ID 
Where ep.Process_ID = ISNULL(@Process_ID,ep.Process_ID)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Securities_ToProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Securities_ToProcess]
GO

CREATE PROCEDURE [dbo].[get_Securities_ToProcess]
(
@Process_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN
Declare @CurrentTime as datetime;
SET @CurrentTime = GETDATE();
Select ep.*,@CurrentTime as SecGroup_InQue,epl.SecGroup_InProcess,epl.SecGroup_Processed,epl.SecGroup_Requested 
from vw_Queue_Securities_ToProcess  ep Left Join tbl_System_ProcessLog_SecGroups epl ON  ep.SecOrder_ID = epl.SecOrder_ID 
Where ep.Process_ID = ISNULL(@Process_ID,ep.Process_ID)

Update dbo.tbl_System_ProcessLog_SecGroups
SET SecGroup_InQue = @CurrentTime
from vw_Queue_Securities_ToProcess  ep INNER JOIN  tbl_System_ProcessLog_SecGroups epl ON  ep.SecOrder_ID = epl.SecOrder_ID 
Where ep.Process_ID = @Process_ID

END
GO

--------------------------------------------   get security data ----------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Securities_CorpActions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Securities_CorpActions]
GO

CREATE PROCEDURE [dbo].[get_Securities_CorpActions]
(
@SecOrder_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Select *
from vw_InProcess_CorpActions  Where SecOrder_ID = ISNULL(@SecOrder_ID,SecOrder_ID)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Securities_Dividends]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Securities_Dividends]
GO

CREATE PROCEDURE [dbo].[get_Securities_Dividends]
(
@SecOrder_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Select *
from vw_InProcess_Dividends  Where SecOrder_ID = ISNULL(@SecOrder_ID,SecOrder_ID)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Securities_IdentifiedStraddles]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Securities_IdentifiedStraddles]
GO

CREATE PROCEDURE [dbo].[get_Securities_IdentifiedStraddles]
(
@SecOrder_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Select *
from vw_InProcess_IdentifiedStraddles  Where SecOrder_ID = ISNULL(@SecOrder_ID,SecOrder_ID)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Securities_M1s]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Securities_M1s]
GO

CREATE PROCEDURE [dbo].[get_Securities_M1s]
(
@SecOrder_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Select *
from vw_InProcess_M1s  Where SecOrder_ID = ISNULL(@SecOrder_ID,SecOrder_ID)
Order By Tran_Client_Open_ID ASC, Tran_Quantity

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Securities_InProcessSecurities]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Securities_InProcessSecurities]
GO

CREATE PROCEDURE [dbo].[get_Securities_InProcessSecurities]
(
@SecOrder_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Select *
from vw_InProcess_Securities  Where SecOrder_ID = ISNULL(@SecOrder_ID,SecOrder_ID)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Securities_SecurityMasters]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Securities_SecurityMasters]
GO

CREATE PROCEDURE [dbo].[get_Securities_SecurityMasters]
(
@SecOrder_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Select *
from vw_InProcess_SecurityMasters  Where SecOrder_ID = ISNULL(@SecOrder_ID,SecOrder_ID)
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Securities_Transactions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Securities_Transactions]
GO

CREATE PROCEDURE [dbo].[get_Securities_Transactions]
(
@SecOrder_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Select *
from vw_InProcess_Transactions  Where SecOrder_ID = ISNULL(@SecOrder_ID,SecOrder_ID)
Order by Tran_Open_Trade_Date  ASC, Tran_Client_Open_ID ASC, Tran_Close_Trade_Date ASC,
 Tran_Client_Close_ID ASC, Tran_Quantity
 
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[upd_Securities_Transactions]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[upd_Securities_Transactions]
GO

CREATE PROCEDURE [dbo].[upd_Securities_Transactions]
(
@SecOrder_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Select *
from vw_InProcess_Transactions  Where SecOrder_ID = @SecOrder_ID


END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Entity_ByProcessID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Entity_ByProcessID]
GO

CREATE PROCEDURE [dbo].[get_Entity_ByProcessID]
(
@Process_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

SELECT        dbo.tbl_System_ProcessLog_Entities.Process_ID, dbo.tbl_System_Entity.Entity_Name, dbo.tbl_System_ProcessLog_Entities.Entities_FromDate, 
                         dbo.tbl_System_ProcessLog_Entities.Entities_ToDate, dbo.tbl_System_ProcessLog_Entities.Entities_MaxDate
                         ,tbl_System_ProcessLog_Entities.Entities_Requested,tbl_System_ProcessLog_Entities.Entities_InQue
                         ,tbl_System_ProcessLog_Entities.Entities_InProcess,tbl_System_ProcessLog_Entities.Entities_Processed
FROM            dbo.tbl_System_Entity INNER JOIN
                         dbo.tbl_System_ProcessLog_Entities ON dbo.tbl_System_Entity.Entity_ID = dbo.tbl_System_ProcessLog_Entities.Entity_ID
                         Where tbl_System_ProcessLog_Entities.Process_ID = @Process_ID



END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Reporting_Views]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Reporting_Views]
GO

CREATE PROCEDURE [dbo].[get_Reporting_Views]
(
@Report_Schema varchar(50) = null
)
AS
SET NOCOUNT ON
BEGIN

SELECT  TABLE_NAME
FROM information_schema.TABLES
Where TABLE_TYPE = 'VIEW' and TABLE_NAME like @Report_Schema+'%'

END
GO


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Reporting_Data]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Reporting_Data]
GO

CREATE PROCEDURE [dbo].[get_Reporting_Data]
(
@Report_View varchar(100) = null,
@Process_ID BIGINT = null,
@SecOrderIDs varchar(max) = null
)
AS
SET NOCOUNT ON
BEGIN

DECLARE @strSQL as nvarchar(max)
SET @strSQL = 'SELECT * FROM '+@Report_View + ' WHERE Process_ID = ' + CAST(@Process_ID as varchar(20))
SET @strSQL = @strSQL + ' and SecOrder_ID in ' + @SecOrderIDs
EXEC sp_executesql  @strSQL

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Entities_All]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Entities_All]
GO

CREATE PROCEDURE [dbo].[get_Entities_All]

AS
SET NOCOUNT ON
BEGIN


Select epl.*,e.Entity_Name
from tbl_System_Entity e INNER JOIN tbl_System_ProcessLog_Entities epl ON  e.Entity_ID = epl.Entity_ID 

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[get_Securities_All]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[get_Securities_All]
GO

CREATE PROCEDURE [dbo].[get_Securities_All]
(
@Process_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Select *
 from tbl_System_ProcessLog_SecGroups ep 
Where ep.Process_ID = ISNULL(@Process_ID,ep.Process_ID)



END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_Reprocess_Entity]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[set_Reprocess_Entity]
GO

CREATE PROCEDURE [dbo].[set_Reprocess_Entity]
(
@Process_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Update tbl_System_ProcessLog_Entities 
SET Entities_InQue = NULL, Entities_InProcess = NULL, Entities_Processed = NULL
Where Process_ID = @Process_ID

Update tbl_System_ProcessLog_SecGroups 
SET SecGroup_InQue = NULL , SecGroup_InProcess = NULL , SecGroup_Processed = NULL
Where Process_ID = @Process_ID

END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[set_Reprocess_Security]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[set_Reprocess_Security]
GO

CREATE PROCEDURE [dbo].[set_Reprocess_Security]
(
@Process_ID BIGINT = null,
@SecOrder_ID BIGINT = null
)
AS
SET NOCOUNT ON
BEGIN

Update tbl_System_ProcessLog_Entities 
SET Entities_InQue = NULL, Entities_InProcess = NULL, Entities_Processed = NULL
Where Process_ID = @Process_ID

Update tbl_System_ProcessLog_SecGroups 
SET SecGroup_InQue = NULL , SecGroup_InProcess = NULL , SecGroup_Processed = NULL
Where Process_ID = @Process_ID and SecOrder_ID =  @SecOrder_ID

END
GO
